from typing import List, Dict, Tuple, Optional
import pandas as pd
import numpy as np
from app.models.grade import StudentGrade, Grade, Student, Exam
from config import Config


class GradeService:
    @staticmethod
    def validate_excel_columns(df: pd.DataFrame) -> Tuple[bool, str]:
        """验证Excel文件是否包含必要的列"""
        required_columns = {'姓名', '学号'}
        missing_columns = required_columns - set(df.columns)
        if missing_columns:
            return False, f"缺少必要的列: {', '.join(missing_columns)}"
        return True, ""

    @staticmethod
    def validate_student_info(row_idx: int, name: str, code: str) -> None:
        """验证学生信息的有效性"""
        if pd.isna(name) or str(name).strip() == '':
            raise ValueError(f"第 {row_idx} 行：姓名不能为空")
        if pd.isna(code) or str(code).strip() == '':
            raise ValueError(f"第 {row_idx} 行：学号不能为空")

    @staticmethod
    def is_valid_score(score) -> bool:
        """检查分数是否有效"""
        if pd.isna(score) or score == '缺考' or score == '-':
            return True
        try:
            score_float = float(score)
            return 0 <= score_float <= 100
        except (ValueError, TypeError):
            return False

    @staticmethod
    def parse_score(score, row_idx: int, exam_code: str):
        """解析成绩，返回解析后的分数或None（缺考）"""
        if pd.isna(score) or score == '缺考' or score == '-':
            return None
        try:
            score_float = float(score)
            if 0 <= score_float <= 100:
                return score_float
            raise ValueError(f"第 {row_idx} 行的 {exam_code} 成绩超出范围 (0-100)")
        except (ValueError, TypeError):
            raise ValueError(f"第 {row_idx} 行的 {exam_code} 成绩格式无效")

    @staticmethod
    def read_as_details(file_path: str, sheet: str = None) -> Tuple[List[StudentGrade], List[Dict]]:
        """读取Excel文件并转换为StudentGrade对象列表"""
        df = pd.read_excel(file_path, sheet_name=sheet)
        is_valid, error_msg = GradeService.validate_excel_columns(df)
        if not is_valid:
            raise ValueError(error_msg)

        grades = []
        errors = []
        exam_columns = [col for col in df.columns if col not in ['姓名', '学号', '班级']]

        for idx, row in df.iterrows():
            row_idx = idx + 2  # Excel行号从1开始，标题占一行
            try:
                name = str(row['姓名']).strip()
                code = str(row['学号']).strip()
                group = str(row.get('班级', '')).strip()

                GradeService.validate_student_info(row_idx, name, code)

                for exam_code in exam_columns:
                    score = row[exam_code]
                    if not GradeService.is_valid_score(score):
                        errors.append({
                            'row': row_idx,
                            'name': name,
                            'code': code,
                            'exam': exam_code,
                            'error': f"成绩格式无效: {score}"
                        })
                        continue

                    parsed_score = GradeService.parse_score(score, row_idx, exam_code)
                    grades.append(StudentGrade(
                        stu_name=name,
                        stu_code=code,
                        group_name=group,
                        exam_code=exam_code,
                        exam_score=parsed_score
                    ))
            except ValueError as e:
                errors.append({
                    'row': row_idx,
                    'error': str(e)
                })

        return grades, errors

    @staticmethod
    def cal_rank(data: List[StudentGrade], group_name: str, exam_code: str, stu_code: str) -> int:
        """计算学生在指定考试中的排名"""
        group_grades = [g.exam_score for g in data
                        if g.group_name == group_name and g.exam_code == exam_code and g.exam_score is not None]
        if not group_grades:
            return 0
        student_score = next((g.exam_score for g in data
                              if g.stu_code == stu_code and g.exam_code == exam_code), None)
        if student_score is None:
            return 0
        return sorted(group_grades, reverse=True).index(student_score) + 1

    @staticmethod
    def cal_statistics(data: List[StudentGrade], group_name: str, exam_code: str) -> Tuple[float, float, float]:
        """计算平均分、中位数和及格率"""
        group_grades = [g.exam_score for g in data
                        if g.group_name == group_name and g.exam_code == exam_code and g.exam_score is not None]
        if not group_grades:
            return 0, 0, 0

        avg = np.mean(group_grades)
        median = np.median(group_grades)
        pass_rate = len([g for g in group_grades if g >= 60]) / len(group_grades) * 100

        return round(avg, 2), round(median, 2), round(pass_rate, 2)

    @staticmethod
    def get_grade_level(score: float, grade_levels=None) -> str:
        """根据分数获取等级"""
        if grade_levels is None:
            grade_levels = Config.DEFAULT_GRADE_LEVELS

        if score is None:
            return '缺考'

        for level in grade_levels:
            if level['min'] <= score <= level['max']:
                return level['name']
        return 'D'  # 默认最低等级

    @staticmethod
    def get_detail_grade_by_stu(data: List[StudentGrade], group_name: str) -> List[Student]:
        """获取学生详细成绩信息"""
        students = {}
        for grade in data:
            if grade.group_name != group_name:
                continue

            if grade.stu_code not in students:
                students[grade.stu_code] = Student(
                    name=grade.stu_name,
                    code=grade.stu_code,
                    grades=[]
                )

            rank = GradeService.cal_rank(data, group_name, grade.exam_code, grade.stu_code)
            grade_level = GradeService.get_grade_level(grade.exam_score)

            students[grade.stu_code].add_grade(Grade(
                stu_code=grade.stu_code,
                stu_name=grade.stu_name,
                exam_code=grade.exam_code,
                score=grade.exam_score if grade.exam_score is not None else 0,
                grade=grade_level,
                rank=rank
            ))

        return list(students.values())

    @staticmethod
    def get_detail_statistics_by_group(data: List[StudentGrade], group_name: str) -> List[Exam]:
        """获取班级统计信息"""
        exam_stats = {}
        for grade in data:
            if grade.group_name != group_name:
                continue

            if grade.exam_code not in exam_stats:
                exam_stats[grade.exam_code] = []
            if grade.exam_score is not None:
                exam_stats[grade.exam_code].append((grade.stu_name, grade.exam_score))

        results = []
        for exam_code, scores in exam_stats.items():
            if not scores:
                continue

            avg, median, pass_rate = GradeService.cal_statistics(data, group_name, exam_code)
            actual_num = len(scores)
            expected_num = len([g for g in data if g.group_name == group_name and g.exam_code == exam_code])

            # 计算各等级人数
            levels_item = {}
            for _, score in scores:
                grade = GradeService.get_grade_level(score)
                if grade not in levels_item:
                    levels_item[grade] = []
                levels_item[grade].append(score)

            # 获取前三名和后三名
            sorted_scores = sorted(scores, key=lambda x: x[1], reverse=True)
            top3 = sorted_scores[:3]
            bottom3 = sorted_scores[-3:]

            results.append(Exam(
                code=exam_code,
                avg_score=avg,
                median_score=median,
                pass_rate=pass_rate,
                actual_num=actual_num,
                expected_num=expected_num,
                levels_item=levels_item,
                top3=top3,
                bottom3=bottom3
            ))

        return results
